package com.mouse.mapper;
import com.mouse.entity.DoubleAlleles;
import com.mouse.entity.GeneAllele;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mouse.entity.Phenotype;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author godson
 * @since 2022-08-07
 */
public interface GeneAlleleMapper extends BaseMapper<GeneAllele> {

    @Select("SELECT * FROM(Select DISTINCT ga.* FROM (SELECT gcom.* FROM gene_category gca LEFT JOIN genecategory_genecomposition gg ON gca.gid=gg.gid LEFT JOIN gene_composition gcom ON gg.gcid=gcom.gcid WHERE gca.gid=#{gid}) gcom_result\n" +
            "LEFT JOIN gene_composition_double_alleles gcomda ON gcom_result.gcid=gcomda.gcid LEFT JOIN double_alleles da on gcomda.did=da.did "+
            "LEFT JOIN double_alleles_gene_allele daga ON da.did=daga.did LEFT JOIN gene_allele ga ON ga.aid=daga.aid) a WHERE a.aid IS NOT NULL")
    List<GeneAllele> getGeneAllelesByGid( @Param("gid") Long gid);

    @Select("SELECT ga.* FROM (SELECT da.* FROM gene_composition gcom LEFT JOIN gene_composition_double_alleles gcda ON gcom.gcid=gcda.gcid LEFT JOIN double_alleles da ON gcda.did=da.did WHERE gcom.gcid=#{gcid}) gc_result\n" +
            "LEFT JOIN double_alleles_gene_allele daga ON gc_result.did = daga.did LEFT JOIN gene_allele ga ON daga.aid= ga.aid")
    List<GeneAllele> getGeneAllelesByGcid(@Param("gcid") Long gcid);

    /***
     * 3.2.3
     * @param did
     * @return
     */
    @Select("SELECT ga.* FROM (SELECT daga.* FROM double_alleles da LEFT JOIN double_alleles_gene_allele daga ON da.did=daga.did WHERE da.did=#{did})\n" +
            "res LEFT JOIN gene_allele ga ON res.aid=ga.aid")
    List<GeneAllele> getGeneAllelesByDid(@Param("did") Long did);


    /***
     * 4.1
     * @param alleleName
     * @return
     */
    @Select("SELECT * FROM gene_allele WHERE gene_allele.allelename=#{alleleName}")
    List<GeneAllele> getGeneAllelesByName(@Param("alleleName") String alleleName);
}
